import pandas as pd
import pymysql


def open():
    conn=pymysql.connect(
        host='localhost',   #主机名
        port=3306,          #端口号
        user='root',        #用户名
        password='xjy123456',#密码
        autocommit=True        # 自动提交更改
    )
    return conn
#导入数据库
def shuru_2(path):
    conn=open()
    # 创建游标对象
    cursor = conn.cursor()
    # 选择数据库
    conn.select_db('xjy')

    # 读取Excel文件
    df = pd.read_excel(path, header=None)
    # 从第二行开始导入数据
    for index, row in df.iloc[1:].iterrows():
        # 跳过整行都是空的情况
        if not row.dropna().empty:
            values = []
            params = []
            for value in row:
                if pd.isna(value):
                    # 使用NULL值替换NaN
                    values.append('NULL')
                else:
                    # 添加占位符
                    values.append('%s')
                    params.append(value)
            # 构建插入语句模板
            sql = "INSERT INTO taekwondo VALUES ({})".format(', '.join(values))
            # 执行参数化查询
            cursor.execute(sql, params)

    cursor.close()
    conn.close()
    return 0

#出现表格
def kais():#开始时出现表格
    conn = open()
    # 创建游标对象
    cursor = conn.cursor()
    # 选择数据库
    conn.select_db('xjy')
    sql = "select * from taekwondo;"
    cursor.execute(sql)
    result = cursor.fetchall()  # 获取记录
    row = cursor.rowcount  # 获取记录格数，设置表格行数
    vol = len(result[0])  # 取字段数，设置表格列数
    cursor.close()  # 关闭游标
    conn.close()  # 关闭连接
    return  row,vol,result
#导出
def daochu(path):
    conn=open()
    conn.select_db('xjy')
    # 从MySQL数据库中读取数据到DataFrame
    query = 'SELECT * FROM taekwondo'
    df = pd.read_sql_query(query, conn)
    # 关闭数据库连接
    conn.close()
    # 将DataFrame保存为Excel文件
    df.to_excel(path+'/'+'output.xlsx', index=False, header=True, encoding='utf-8')
    return 1

#查询每场得人物信息
def execute_query():
    conn = open()
    cursor = conn.cursor()
    conn.select_db('xjy')
    # with self.connection.cursor() as cursor:
    sql = "SELECT changdi,changdihao,lunci,jibie,bluename,blue_danwei,redname,red_danwei,bisaixuhao,blue_bianhao,red_bianhao FROM taekwondo"
    cursor.execute(sql)
    cursor.close()
    conn.close()
    # print(cursor.fetchall()[0])
    return cursor.fetchall()
#查询第一行得信息
def execute_query_one():
    conn = open()
    cursor = conn.cursor()
    conn.select_db('xjy')
    # with self.connection.cursor() as cursor:
    sql = "SELECT changdi,changdihao,lunci,jibie,bluename,blue_danwei,redname,red_danwei,bisaixuhao,blue_bianhao,red_bianhao FROM taekwondo;"
    cursor.execute(sql)
    cursor.close()
    conn.close()
    # print(cursor.fetchall()[0])
    return cursor.fetchall()[0]

#点击下一场后数据库更改
def gengai(a_1,a_2,a_3,a_4,a_5,a_6,a_7,a_8,a_9,a_10,a_11,a_12,a_13,a_14,a_15,a_16,a_17,a_18,a_19,a_20,a_21,a_22,a_23,a_24,a_25):
    conn = open()
    cursor = conn.cursor()
    conn.select_db('xjy')
    sql ="update taekwondo set  bisaizhuangtai='已结束',winer='{0}',win_fs='{1}',blue_socre='{2}',red_socre='{3}',blue_koufen='{4}',red_koufen='{5}',\
                      blue_score1='{6}',red_score1='{7}',blue_koufen1='{8}',red_koufen1='{9}',blue_score2='{10}',red_score2='{11}',\
                      blue_koufen2='{12}',red_koufen2='{13}',blue_score3='{14}',red_score3='{15}',blue_koufen3='{16}',red_koufen3='{17}',\
                      blue_score4='{18}',red_score4='{19}',blue_koufen4='{20}',red_koufen4='{21}',bluewinnum='{22}',redwinnum='{23}'  where bisaixuhao='{24}'\
                      ;".format(a_1,a_2,a_3,a_4,a_5,a_6,a_7,a_8,a_9,a_10,a_11,a_12,a_13,a_14,a_15,a_16,a_17,a_18,a_19,a_20,a_21,a_22,a_23,a_24,a_25)
    cursor.execute(sql)
    print(sql)
    cursor.close()
    conn.close()
#胜利者修改
def xiu(a1,a2,a3,a4):
    conn=open()
    cursor = conn.cursor()
    conn.select_db('xjy')
    sql="update taekwondo set  blue_bianhao='{0}',blue_danwei='{1}',bluename='{2}'  where bluename='{3}胜者';".format(a1,a2,a3,a4)
    cursor.execute(sql)
    sql="update taekwondo set  red_bianhao='{0}',red_danwei='{1}',redname='{2}' where redname='{3}胜者';".format(a1,a2,a3,a4)
    cursor.execute(sql)
    # print(sql)
    cursor.close()
    conn.close()

#输出场地
def chang():
    conn=open()
    cursor = conn.cursor()
    conn.select_db('xjy')
    # 从MySQL数据库中读取数据到DataFrame
    sql= 'SELECT DISTINCT changdi FROM taekwondo;'
    cursor.execute(sql)
    result = cursor.fetchall()
    a=[]
    for i in result:
        a.append(i[0])
    cursor.close()
    conn.close()
    return  a

#场地查询
def cha(a1):
    conn = open()
    # 创建游标对象
    cursor = conn.cursor()
    # 选择数据库
    conn.select_db('xjy')
    sql = "select * from taekwondo where changdi='{}' and bisaizhuangtai='等待中';".format(a1)
    cursor.execute(sql)
    result = cursor.fetchall()  # 获取记录
    row = cursor.rowcount  # 获取记录格数，设置表格行数
    vol = len(result[0])  # 取字段数，设置表格列数
    cursor.close()  # 关闭游标
    conn.close()  # 关闭连接
    return  row,vol,result


if __name__=="__main__":
    # path=r"C:\Users\xjy\Desktop\对阵表1.xlsx"

    # shuru(path)
    # path1=r"C:\Users\xjy\Desktop\bisaixinxi.xls"
    # shuru_2(path1)
    # path3=r"C:\Users\xjy\Desktop"
    # daochu(path3)
    # execute_query()
    chang()